package biz.bbtec.sbm.issuetracking.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import com.opensymphony.xwork2.ActionContext;

import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.issuetracking.bean.AttachmentBean;
import biz.bbtec.sbm.issuetracking.bean.TaskBean;
import biz.bbtec.sbm.user.bean.UserBean;

public class TaskService {
	dbConnection db = new dbConnection();

	public boolean Modify(TaskBean task) {
		boolean flag = false;

		String sql = "update task set name=? , startdate=? , deadline=?, status=? where taskid= "
				+ "'" + task.getTaskid() + "'";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setString(1, task.getName());
			past.setString(2, task.getStartdate());
			past.setString(3, task.getDeadline());
			past.setInt(4, task.getStatus());
			past.executeUpdate();
			past.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean DeleteTask(TaskBean task) throws SQLException {
		boolean flag = false;

		Connection conn = db.getConnection();
		conn.setAutoCommit(false);
		PreparedStatement past = null;
		String sql2 = "delete from task where taskid =" + "'"
				+ task.getTaskid() + "'";
		try {
			past = conn.prepareStatement(sql2);
			past.executeUpdate(sql2);
			conn.commit();
			flag = true;
		} catch (SQLException e) {
			conn.rollback();
			e.printStackTrace();
		} finally {
			conn.setAutoCommit(true);
			conn.close();
			past.close();
			db.closeConnection(conn);
		}
		return flag;
	}

	public List<AttachmentBean> attlist(TaskBean task) {
		List<AttachmentBean> list = new ArrayList<AttachmentBean>();
		String sql = "select attid,title from attachment where taskid = ?";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, task.getTaskid());
			rs = past.executeQuery();
			while (rs.next()) {
				AttachmentBean att = new AttachmentBean();
				att.setAttid(rs.getInt(1));
				att.setTitle(rs.getString(2));
				list.add(att);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public boolean AddTask(TaskBean taskbean) throws ParseException,
			SQLException {

		boolean flag = false;

		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int userid = user.getUserid();

		Connection conn = db.getConnection();

		try {
			PreparedStatement past = null;
			String sql1 = "insert into task(name,deadline,startdate,status) values("
					+ "'"
					+ taskbean.getName()
					+ "'"
					+ ","
					+ "'"
					+ taskbean.getDeadline()
					+ "'"
					+ ","
					+ "'"
					+ taskbean.getStartdate()
					+ "'"
					+ ","
					+ "'"
					+ taskbean.getStatus() + "'" + ")";
			past = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
			past.executeUpdate(sql1);
			ResultSet rs = past.getGeneratedKeys();

			if (rs.next()) {
				int taskid = rs.getInt(1);
				String sql2 = "insert into taskaccess(taskid,userid,level) values("
						+ "'"
						+ taskid
						+ "'"
						+ ","
						+ "'"
						+ userid
						+ "'"
						+ ","
						+ "'" + taskbean.getLevel() + "'" + ")";
				past = conn.prepareStatement(sql2);
				past.executeUpdate(sql2);
				flag = true;
			} else {
				return flag;
			}
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List ShowAllTask(int pagenow, int pagesize) {
		List<TaskBean> list = new ArrayList();
		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int userid = user.getUserid();

		String sql = "select * from task,taskaccess where task.taskid = taskaccess.taskid and taskaccess.userid = '"
				+ userid + " '" + " limit ?,?";
		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			past.setInt(1, pagenow * pagesize);
			past.setInt(2, pagesize);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				double result1 = 0.0;
				double result2 = 0.0;
				TaskBean task = new TaskBean();
				task.setTaskid(Integer.parseInt(rs.getString("taskid")));

				task.setStatus(Integer.parseInt(rs.getString("status")));
				int taskid = task.getTaskid();
				if (task.getStatus() == 0) {
					task.setProgress("100");
				} else {
					String sql2 = "select progress,workamount from workpackage where taskid = "
							+ "'" + taskid + "'";
					try {
						Statement stmt = conn.createStatement();
						ResultSet rs2 = stmt.executeQuery(sql2);
						while (rs2.next()) {
							double workamount = rs2.getInt(2);
							int progress = rs2.getInt(1);
							result1 = result1 + workamount * progress;
							result2 = result2 + workamount;
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
					double result = result1 / (result2 + 0.0000001);
					DecimalFormat df = new DecimalFormat("0");
					String temp = df.format(result + 0.5);
					task.setProgress(temp);
				}

				task.setName(rs.getString("name"));
				task.setStartdate(rs.getString("startdate"));
				task.setDeadline(rs.getString("deadline"));

				list.add(task);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}


	public int CountAllTask() {
		int total = 0;
		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int userid = user.getUserid();
		String sql1 = "select count(*) from task,taskaccess where task.taskid = taskaccess.taskid and taskaccess.userid = '"
				+ userid + " '";
		Connection conn = db.getConnection();
		ResultSet rs = null;
		PreparedStatement past = null;

		try {
			past = conn.prepareStatement(sql1);
			rs = past.executeQuery();
			if (rs.next()) {
				total = rs.getInt(1);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return total;
	}

	public List ShowAllName() {
		List<TaskBean> list = new ArrayList();
		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int userid = user.getUserid();
		String sql = "select taskid,name from task where taskid not in"
				+ " (select task.taskid from task,taskaccess "
				+ "where task.taskid=taskaccess.taskid and taskaccess.userid="
				+ "'" + userid + "'" + ")";
		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				TaskBean task = new TaskBean();
				task.setName(rs.getString("name"));
				task.setTaskid(Integer.parseInt(rs.getString("taskid")));
				list.add(task);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List ShowAllName2() {
		List<TaskBean> list = new ArrayList();
		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		String sql = "select task.taskid,task.name from task,taskaccess "
				+ "where task.taskid=taskaccess.taskid and taskaccess.userid = "
				+ "'" + user.getUserid() + "'";
		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				TaskBean task = new TaskBean();
				task.setName(rs.getString("name"));
				task.setTaskid(Integer.parseInt(rs.getString("taskid")));
				list.add(task);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public TaskBean ShowOneTask(TaskBean taskbean) throws ParseException {
		TaskBean task = new TaskBean();
		String sql = "select * from task where taskid = ? ";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, taskbean.getTaskid());
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			task.setTaskid(Integer.parseInt(rs.getString("taskid")));
			task.setName(rs.getString("name"));
			task.setStartdate(rs.getString("startdate"));
			task.setDeadline(rs.getString("deadline"));
			task.setStatus(Integer.parseInt(rs.getString("status")));
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return task;
	}

	public boolean ChooseTask(List<TaskBean> tasklist) {
		boolean flag = false;

		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int userid = user.getUserid();
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			String sql1 = "delete from taskaccess where userid = " + "'"
					+ user.getUserid() + "'";
			past = conn.prepareStatement(sql1);
			past.executeUpdate(sql1);
			past.close();
			for (int i = 0; i < tasklist.size(); i++) {
				String sql2 = "insert into taskaccess(taskid,userid) values "
						+ "(" + "'" + tasklist.get(i).getTaskid() + "'" + ","
						+ "'" + user.getUserid() + "'" + ")";
				past = conn.prepareStatement(sql2);
				past.executeUpdate();
				past.close();
			}
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List ShowALLInolve(int taskid) {
		List<UserBean> list = new ArrayList<UserBean>();

		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int userid = user.getUserid();

		String sql1 = " select * from user,taskaccess where  user.userid = taskaccess.userid and taskaccess.taskid = "
				+ "'" + taskid + "'";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			rs = past.executeQuery(sql1);
			while (rs.next()) {
				UserBean involve = new UserBean();
				involve.setName(rs.getString("name"));
				involve.setPhone(rs.getString("phone"));
				involve.setEmail(rs.getString("email"));
				involve.setUserid(rs.getInt("userid"));
				list.add(involve);
			}
			rs.close();
			past.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public List ShowALLUser(int taskid) {
		List<UserBean> list = new ArrayList<UserBean>();

		Map session = ActionContext.getContext().getSession();
		UserBean user = (UserBean) session.get("user");
		int orgid = user.getOrgid();

		String sql1 = " select * from user where userid not in "
				+ "(select user.userid from user,taskaccess where user.userid = taskaccess.userid "
				+ "and taskaccess.taskid = " + "'" + taskid + "'" + ")"
				+ " and orgid = " + "'" + orgid + "'";

		Connection conn = db.getConnection();
		PreparedStatement past = null;
		ResultSet rs = null;
		try {
			past = conn.prepareStatement(sql1);
			rs = past.executeQuery(sql1);
			while (rs.next()) {
				UserBean userbean = new UserBean();
				userbean.setName(rs.getString("name"));
				userbean.setPhone(rs.getString("phone"));
				userbean.setEmail(rs.getString("email"));
				userbean.setUserid(rs.getInt("userid"));
				list.add(userbean);
			}
			rs.close();
			past.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public String showTaskName(int taskid) {
		String name = new String();
		String sql1 = "select name from task where taskid = ?";
		Connection conn = db.getConnection();
		PreparedStatement ps1 = null;
		ResultSet rs1 = null;
		try {
			ps1 = conn.prepareStatement(sql1);
			ps1.setInt(1, taskid);
			rs1 = ps1.executeQuery();
			if (rs1.next()) {
				name = rs1.getString(1);
			} else {
				name = null;
			}
			rs1.close();
			ps1.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return name;
	}


}
